/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import java.sql.*;
import java.util.ArrayList;
import Module.*;

/**
 *
 * @author Vg@rd
 */
public class SessionController {

    private Database database = new Database();

    public boolean registerSession(Session session) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());

            String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "Insert into session values(DEFAULT,?,?,?,?,?,?)",autogen);
            statement.setString(2, session.getName());
            statement.setString(3, session.getDate());
            statement.setString(4, session.getPlace());
            statement.setString(5, session.getStartTime());
            statement.setString(6, session.getEndTime());
            statement.setInt(1, session.getProjectId());
            statement.executeUpdate();
            conn.close();
            return true;
        } catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    public User getProjectLeaderToSession(int sessionId){
        Connection conn = null;
        User u = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userid=userssession.userId) "+
                    "where userssession.roleId=1 and userssession.sessionId=?");
            statement.setInt(1,sessionId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
                u = new User(rs.getInt(1),rs.getString(2),rs.getString(3));
                conn.close();
                return u;
            }
        }catch(Exception e){
            System.err.println(e);
        }return u;
    }

    public User getFasilitatorToSession(int sessionId){
        Connection conn = null;
        User u = null;

        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userId=userssession.userId) "+
                    "where userssession.roleId=2 and userssession.sessionId=?");
            statement.setInt(1,sessionId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {           
                u = new User(rs.getInt(1),rs.getString(2),rs.getString(3));}
                conn.close();
                return u;

        }catch(Exception e){
            System.err.println(e);
        }return u;
    }
    public User getSecretariesToSession(int sessionId){
        Connection conn = null;
        User u = null;

        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userId=userssession.userId) "+
                    "where userssession.roleId=3 and userssession.sessionId=?");
            statement.setInt(1,sessionId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
                u = new User(rs.getInt(1),rs.getString(2),rs.getString(3));
                conn.close();
                return u;
            }
        }catch(Exception e){
            System.err.println(e);
        }return u;
    }
    public ArrayList<User> getExpertsToSession(int sessionId){
        Connection conn = null;
        ArrayList<User> u = new ArrayList<User>();

        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userId=userssession.userId) "+
                    "where userssession.roleId=4 and userssession.sessionId=?");
            statement.setInt(1,sessionId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()) {
                System.out.println("Dette er eksperter!!");
                u.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3)));
            }
            conn.close();
            return u;
        }catch(Exception e){
            System.err.println(e);
        }return u;
    }


    public boolean editSession(Session session) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "update session set sessionName=?, dato=?, place=?, projectId=?, "
                    + "startTime=?, endTime=? where sessionId=?");
            statement.setString(1, session.getName());
            statement.setString(2, session.getDate());
            statement.setString(3, session.getPlace());
            statement.setInt(4, session.getProjectId());
            statement.setString(5, session.getStartTime());
            statement.setString(6, session.getEndTime());
            statement.setInt(6, session.getId());
            statement.executeUpdate();
            conn.close();
            return true;

        } catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    public boolean deleteSession(Session session){
        Connection conn = null;
        //Controller controller = new Controller();

        try{
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement("delete from session where sessionId =?");
            statement.setInt(1,session.getId());
            //controller.getChattlog().deleteChattLog(controller.getChattlog().getChattLogToSession(session));
            //controller.getDecisionlist().deletDecisionList(controller.getDecisionlist().getDecisionListToSession(session));
            statement.executeUpdate();
            conn.close();
            return true;
        }catch(Exception e){
            System.err.println(e);
        }


        return false;
    }


    public ArrayList<Session> getSessionsToProject(Project p) {
        Connection conn = null;
        ArrayList<Session> sessions = new ArrayList<Session>();
        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                "select * from session where projectId =? ");
            statement.setInt(1, p.getId());
            ResultSet r = statement.executeQuery();
            while (r.next()) {
                Session s = new Session(r.getInt(1),r.getInt(2), r.getString(3),r.getString(4), 
                        r.getString(5),r.getString(6),r.getString(7));
                sessions.add(s);
            }

            conn.close();
            return sessions;
        } catch (Exception e) {
            System.err.println(e);
        }
        return sessions;
    }
    public void setUsersToSession(int sessionId, ArrayList<User> users, int roleId){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(
                    "insert into usersSession values(?,?,?)");
            statement.setInt(2, sessionId);
            statement.setInt(3, roleId);
            for(int i=0; i<users.size();i++){
                statement.setInt(1, users.get(i).getId());
                statement.executeUpdate();
            }
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }

}
